OpenEdge Data Management:<br />DataServer for ODBC Sending SQL statements directly to the data source

Progress uses stored-procedure syntax to allow you to send extended SQL statements directly to a data source. The DataServer uses the RUN STORED–PROCEDURE statement with the send–sql–statement option to pass SQL statements to the data source. Although Progress SQL allows you to use SQL statements, this option gives you access to your data source vendor’s SQL syntax and its extensions to SQL, such as Transact–SQL for Sybase. For example, you can issue SQL statements from within Progress procedures to modify the data definitions of your data source. You can send multiple SQL statements by concatenating them and passing the string as a single parameter to the send–sql–statement option.

You use the RUN STORED–PROC statement with the send–sql–statement option and pass the SQL statements as a parameter. The syntax of the statement must be valid SQL syntax for the underlying database and must follow SQL naming and case conventions. For example, this code passes a SELECT statement as a parameter:

DEFINE VAR handle1 AS INTEGER. RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE ("SELECT name, address, city, state, postal-code FROM customer WHERE credit-limit >= 500"). FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1: DISPLAY proc-text-buffer. END. CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

DEFINE VAR handle1 AS INTEGER.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
("SELECT name, address, city, state, postal-code
FROM customer WHERE credit-limit >= 500").
FOR EACH proc-text-buffer WHERE PROC-HANDLE = handle1:
		DISPLAY proc-text-buffer.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

This example returns the name, address, city, state, and postal code for all customers whose max–credit is greater than or equal to $500. You must read the results into a buffer as you would with a stored procedure called by a Progress procedure. You can read the results into the proc–text–buffer defined by Progress, as in the example above, or you can define your own buffer from within your data source that accepts a data type other than the CHARACTER data type.

The following example illustrates returning database results into the proc–text–buffer and converting the results to the INTEGER data type:

DEFINE VAR handle1 AS INTEGER. RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE ("SELECT max (cust_num) FROM customer"). FOR EACH proc-text-buffer: imax = int (substring (proc-text, 1, 3)). DISPLAY imax. END. CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

DEFINE VAR handle1 AS INTEGER.
RUN STORED-PROC send-sql-statement handle1 = PROC-HANDLE
("SELECT max (cust_num) FROM customer").
FOR EACH proc-text-buffer:
		imax = int (substring (proc-text, 1, 3)).
		DISPLAY imax.
END.
CLOSE STORED-PROC send-sql-statement WHERE PROC-HANDLE = handle1.

The DataServer passes the SQL statement directly to the ODBC data source. The Progress Compiler does not process it, so errors occur only at run time and not when you compile a procedure.